package com.example.myapplication.chap05;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.os.Bundle;
import android.view.View;
import android.widget.Button;
import android.widget.TextView;
import android.widget.Toast;

import androidx.appcompat.app.AppCompatActivity;

import com.example.myapplication.R;

//【例5-6前一步骤】数据库记录的添加、查询、删除
public class DB2Activity extends AppCompatActivity  {
    Button createdb,deletedb,queryBtn,deleteItemBtn,insertBtn;
    TextView txt;
    MySQLDatabase myc;
    DatabaseHelper myOpenHelper;
    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.diarydb);
        createdb = (Button)findViewById(R.id.createdb);
        deletedb = (Button)findViewById(R.id.deletedb);
        queryBtn =(Button)findViewById(R.id.querydb);
        deleteItemBtn = (Button)findViewById(R.id.deleteItemBtn);
        insertBtn = (Button)findViewById(R.id.insertdb);
        txt = (TextView)findViewById(R.id.txt);
        createdb.setOnClickListener(new mClick());
        deletedb.setOnClickListener(new mClick());
        queryBtn.setOnClickListener(new mClick());
        deleteItemBtn.setOnClickListener(new mClick());
        insertBtn.setOnClickListener(new mClick());
        myOpenHelper = new DatabaseHelper(this);
    }
    class mClick implements View.OnClickListener {
        @Override
        public void onClick(View view) {
            if(view.getId()==R.id.createdb){
                myc= new MySQLDatabase();
                Toast.makeText(DB2Activity.this, "数据库和数据表创建成功", Toast.LENGTH_SHORT).show();
            }else if(view.getId()==R.id.deletedb){
                deleteDatabase(Database_name);
                txt.setText("删除数据库成功");
                Toast.makeText(DB2Activity.this, "数据库和数据表删除成功", Toast.LENGTH_SHORT).show();
            }else if(view.getId()==R.id.querydb){
                myc.showItems();
            }else if(view.getId()==R.id.deleteItemBtn){
                myc.deleteItem();
            }else if(view.getId()==R.id.insertdb){
                myc.insertItem();
            }
        }
    }
    class  DatabaseHelper extends SQLiteOpenHelper {
        public DatabaseHelper(Context context){
            super(context,Database_name,null,Database_Version);
        }
        @Override
        public void onCreate(SQLiteDatabase sqLiteDatabase) {
        }
        @Override
        public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {
        }
    }
    private static final String Database_name = "eBook.db";//数据库名
    private static final  String TABLE_NAME = "diary"; //数据表名
    private static final  String ID = "nid";           //ID编号
    private static final  String TITLE = "title";      //标题
    private static final  String BODY = "body"; 		 //正文
    private static final  int Database_Version = 1;

    private class MySQLDatabase{
        SQLiteDatabase db;
        private MySQLDatabase(){
            try {
                int mode = Context.MODE_PRIVATE;
                //创建数据库
                db = openOrCreateDatabase(Database_name, mode, null);
                //SQL语句
                String DATABASE_CREATE="CREATE TABLE IF NOT EXISTS " + TABLE_NAME + " (" +
                         ID  + " INTEGER primary key autoincrement," +
                         TITLE + " text not null, " +
                         BODY + " text not null);";
                db.execSQL(DATABASE_CREATE);//创建数据表
                txt.setText("创建数据库成功");
            }catch (SQLException e){
                e.printStackTrace();
                txt.setText("创建数据库失败");
            }
        }
        /*
         * 插入两条数据
         */
        private void insertItem() {
            try {
                ContentValues values = new ContentValues();
                values.put("TITLE", "Android");
                values.put("BODY", "发展真是迅速啊");
                db.insert(TABLE_NAME, ID, values);
                values.put("TITLE", "城市");
                values.put("BODY", "发展真是迅速啊");
                db.insert(TABLE_NAME, ID, values);
                txt.setText("插入两条数据成功");
            }catch (SQLException e){txt.setText("插入两条数据失败");}
        }
        /*
         * 删除符合条件的数据
         */
        private void deleteItem() {
            try {
                db.delete(TABLE_NAME, " TITLE='城市'", null);
                txt.setText("删除数据成功");
            }catch (SQLException e){txt.setText("删除数据失败");}
        }
        /*
         * 查询数据表
         */
        private void showItems() {
            try {
                String[] col = {TITLE, BODY};
                Cursor cursor = db.query(TABLE_NAME, col, null, null, null, null, null);
                Integer num = cursor.getCount();
                String str = "";
                if (num > 0) {
                    cursor.moveToFirst();
                    str = "第一条记录：\n" + cursor.getString(0) + " " + cursor.getString(1);
                }
                String result = "共有" + Integer.toString(num) + "条记录\n" + str;
                txt.setText(result);
            }catch (SQLException e){txt.setText("查询数据失败");}
        }
    }
}
